package com.zgm.ch09.jdbc.jdbcTemplate.query._4_2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;


import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;




public class JdbcTemplateDemo2 {
	
	private static Log logger = LogFactory.getLog(JdbcTemplateDemo2.class);
	private JdbcTemplate jdbcTemplate;
	
	public static void main(String[] args) {
        ApplicationContext ac = new ClassPathXmlApplicationContext("_4_2-context.xml", JdbcTemplateDemo2.class);
        JdbcTemplateDemo2 demo = (JdbcTemplateDemo2) ac.getBean("jdbcTemplateDemo2");
        //demo.runExecute();
        demo.runQuery();
        //demo.runCall();
        //demo.runUpdate();
//        demo.runBatch();
    }
	
	public void runQuery() {
		String countStr = "select count(*) from t_customer";
		Long count = (Long) this.jdbcTemplate.query(countStr, new ResultSetExtractor() {
			public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
				if(resultSet.next()) {
					return resultSet.getLong(1);
				}
				
				return null;
			}
		});
		System.out.println(count);

		String namesStr = "select first_name from t_customer";
		List<String> names = this.jdbcTemplate.query(namesStr, new RowMapper() {

			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				
				return rs.getString(1);
			}
			
		});
		System.out.println(names);
		
		
		
		String queryStr = "select first_name, last_name, last_login from t_customer where last_login is null";
		this.jdbcTemplate.query(queryStr, new RowCallbackHandler(){
			public void processRow(ResultSet resultSet) throws SQLException {
				while (resultSet.next()) {
					System.out.println("processRow方法执行了");
					System.out.println(resultSet.getString(1));
					
				}
			}
		});
		
		String machaceksName = (String) this.jdbcTemplate.query(
				new PreparedStatementCreator() {
					public PreparedStatement createPreparedStatement(Connection connection)  throws SQLException {
						String queryStr = "select first_name from t_customer where last_name like ?";
						return connection.prepareStatement(queryStr);
					}
					
				},
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement preparedStatement) throws SQLException {
						preparedStatement.setString(1, "z%");
					}
				},
				new ResultSetExtractor() {
					public Object extractData(ResultSet resultSet) throws SQLException {
						if (resultSet.next()) {
//							return resultSet.getLong(1);
							return resultSet.getString(1);
						}
						
						return null;
					}
				}
		);
		System.out.println(machaceksName);
		
		Long maxId = (Long) this.jdbcTemplate.query(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				return con.prepareStatement("select max(id) from t_customer");
			}
			
		}, new ResultSetExtractor() {

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(rs.next()) {
					return rs.getLong(1);
				}
				
				return null;
			}
			
		});
		System.out.println(maxId);
		
		String firstCustomersNameStr = "select first_name from t_customer where id=?";
		String firstCustomersName = (String) this.jdbcTemplate.query(firstCustomersNameStr, new PreparedStatementSetter() {

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setLong(1, 1002L);
//				ps.setLong(1, 1);
			}
			
		}, new FirstColumnStringExtractor());
		System.out.println(firstCustomersName);
		
		String firstCustomersLastName = (String) this.jdbcTemplate.query(
                "select last_name from t_customer where id=?",
                new Object[]{1000L}, new int[]{Types.INTEGER},/////////////
                new FirstColumnStringExtractor());
        System.out.println(firstCustomersLastName);
        
        System.out.println(this.jdbcTemplate.query(
                "select last_name from t_customer where id=?",
                new Object[]{1000L},////////
                new FirstColumnStringExtractor()
        ));
        
        ////好像也有问题
        this.jdbcTemplate.query(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                return connection.prepareStatement("select last_name from t_customer where last_login is null");
            }
        }, new StringExtractingRowCallbackHandler());
        
        ////好像也有问题
        this.jdbcTemplate.query("select first_name from t_customer where id > ?",
                new PreparedStatementSetter() {
                    public void setValues(PreparedStatement preparedStatement) throws SQLException {
                        preparedStatement.setLong(1, 1000L);
                    }
                },
                new StringExtractingRowCallbackHandler());
        
        this.jdbcTemplate.query("select first_name from t_customer where last_login < ?",
                new Object[] { new java.util.Date() },
                new int[] { Types.TIMESTAMP },
                new StringExtractingRowCallbackHandler());
        this.jdbcTemplate.query("select first_name from t_customer where id=?",
                new Object[] { 1000L },
                new StringExtractingRowCallbackHandler());

        System.out.println(this.jdbcTemplate.query(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                return connection.prepareStatement("select first_name from t_customer");
            }
        }, new FirstColumnStringRowMapper()));
        System.out.println(this.jdbcTemplate.query("select first_name from t_customer where id=?",
                new PreparedStatementSetter() {
                    public void setValues(PreparedStatement preparedStatement) throws SQLException {
                        preparedStatement.setLong(1, 1L);
                    }
                },
                new FirstColumnStringRowMapper()));
        System.out.println(this.jdbcTemplate.query("select first_name from t_customer where id=?",
                new Object[] { 1L },
                new int[] { Types.INTEGER },
                new FirstColumnStringRowMapper()));
        System.out.println(this.jdbcTemplate.query("select first_name from t_customer where id=?",
                new Object[] { 1L },
                new FirstColumnStringRowMapper()));
        
        System.out.println(this.jdbcTemplate.queryForList("select id from t_customer", Long.class));
        System.out.println(this.jdbcTemplate.queryForMap("select * from t_customer where id=1000"));
        
	}
	
	
	private static class FirstColumnStringExtractor implements ResultSetExtractor {

		public Object extractData(ResultSet rs) throws SQLException,
				DataAccessException {
			if (rs.next()) {
				return rs.getString(1);
			}
			return null;
		}
		
	}
	
	private static class StringExtractingRowCallbackHandler implements RowCallbackHandler {
		private static int i = 0;
		private int j = 0;
		public void processRow(ResultSet rs) throws SQLException {
			int k = 0;
			if (rs.next()) {
				i++;
				j++;
				k++;
				System.out.println(rs.getString(1));
				System.out.println("i值=" + i);
				System.out.println("j值=" + j);
				System.out.println("k值=" + k);
			}
		}
		
	}
	
	private static class FirstColumnStringRowMapper implements RowMapper {

		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			
			return rs.getString(1);
		}
		
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}
